<%@ page import="java.sql.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.io.*" %>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm";

Connection connection = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()) {
String newCopyID = null;
String newAccountName = null;

newCopyID = request.getParameter("copyid");
newAccountName = request.getParameter("username");

int user_credit = 0;
Statement statement0 = null;
ResultSet rs0 = null;
statement0 = connection.createStatement();
String query0 = "SELECT Credit FROM Users WHERE AccountName = '" + newAccountName + "';";
rs0 = statement0.executeQuery(query0);
if(rs0.next()) {
user_credit = Integer.parseInt(rs0.getString("Credit"));

}
statement0.close();

if(user_credit > 0) {
int count_record = 0;
Statement statement1 = null;
statement1 = connection.createStatement();
String query1 = "UPDATE Lend SET Status = 'Canceled' WHERE AccountName = '" + newAccountName + "' AND CopyID = " + newCopyID + " AND Status = 'In Stock';";
count_record = statement1.executeUpdate(query1, Statement.NO_GENERATED_KEYS);
statement1.close();

if(count_record != 0) {
out.print("delete_successful");

int count_listed = 0;
Statement statement2 = null;
ResultSet rs2 = null;
statement2 = connection.createStatement();
String query2 = "SELECT COUNT(*) AS Count FROM Lend WHERE AccountName = '" + newAccountName + "' AND Status <> 'Canceled';";
rs2 = statement2.executeQuery(query2);
if(rs2.next()) {
count_listed = Integer.parseInt(rs2.getString("Count"));

}
statement2.close();

if(count_listed % 2 != 0) {
Statement statement3 = null;
statement3 = connection.createStatement();
String query3 = "UPDATE Users SET Credit = Credit - 1 WHERE AccountName = '" + newAccountName + "';";
statement3.executeUpdate(query3, Statement.NO_GENERATED_KEYS);
statement3.close();

}

Statement statement4 = null;
statement4 = connection.createStatement();
String query4 = "UPDATE BookCopies SET Availability = 'N/A' WHERE CopyID = " + newCopyID + ";";
statement4.executeUpdate(query4, Statement.NO_GENERATED_KEYS);
statement4.close();

Statement statement5 = null;
statement5 = connection.createStatement();
String query5 = "UPDATE Buy SET Status = 'Confirmed' WHERE AccountName = '" + newAccountName + "' AND CopyID = " + newCopyID + " AND Status = 'Canceled' ;";
statement5.executeUpdate(query5, Statement.NO_GENERATED_KEYS);
statement5.close();

}
else {
out.print("delete_failed");

}

}
else {
out.print("insufficient_credit");

}

}

connection.close();

}
catch(Exception ex){
out.print(ex);

}
%>